package modbusGreen

import (
    "database/sql"
    "errors"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "reflect"
    "strconv"
    "strings"
)

const (
    mysqlPath   = "root:green@tcp(127.0.0.1:3306)/modbus?charset=utf8"
)

var ORM = Orm{}

func init()  {
    InitDataBase(mysqlPath)
}

func InitDataBase(path string) (err error) {
    db, err := sql.Open("mysql", path)
    if err != nil {
        return err
    }
    ORM = Orm{DB: db}
    return
}

//Orm 我重建的ORM.
type Orm struct {
    *sql.DB
}

const (
    sqlTag = "sql"
)

//Select 查询
func (o *Orm) Select(tableName, condition string, data interface{}, arg ...interface{}) (err error) {
    sql := getSelectSQL(tableName, data)
    sql += condition

    rows, err := o.Query(sql, arg...)
    if err != nil {
        Error.Println(sql, arg, err)
        return err
    }
    Info.Println(sql, arg)

    columns, err := rows.Columns()
    if err != nil {
        return err
    }
    defer rows.Close()
    scans := make([]interface{}, len(columns))
    refs := make([][]byte, len(columns))
    for i := range columns {
        scans[i] = &refs[i]
    }

    if rows.Next() {
        err = rows.Scan(scans...)
    } else {
        return errors.New("<QuerySeter> no row found")
    }

    columnsMp := make(map[string]string)
    for i, col := range columns {
        columnsMp[col] = string(refs[i])
    }

    val := reflect.ValueOf(data)
    typ := reflect.TypeOf(data)
    for i := 0; i < val.Elem().NumField(); i++ {
        name := getFieldName(typ.Elem().Field(i), sqlTag)
        if name[0] == "-" {
            continue
        }
        v := columnsMp[name[0]]
        setStrValue(v, val.Elem().Field(i))

    }
    return
}

//SelectSQL 查询
func (o *Orm) SelectSQL(sql string, data interface{}, arg ...interface{}) (err error) {

    rows, err := o.Query(sql, arg...)
    if err != nil {
        Error.Println(sql, arg, err)
        return err
    }
    Info.Println(sql, arg)

    columns, err := rows.Columns()
    if err != nil {
        return err
    }
    defer rows.Close()
    scans := make([]interface{}, len(columns))
    refs := make([][]byte, len(columns))
    for i := range columns {
        scans[i] = &refs[i]
    }

    if rows.Next() {
        err = rows.Scan(scans...)
    } else {
        return errors.New("<QuerySeter> no row found")
    }

    columnsMp := make(map[string]string)
    for i, col := range columns {
        columnsMp[col] = string(refs[i])
    }

    val := reflect.ValueOf(data)
    typ := reflect.TypeOf(data)
    for i := 0; i < val.Elem().NumField(); i++ {
        name := getFieldName(typ.Elem().Field(i), sqlTag)
        if name[0] == "-" {
            continue
        }
        v := columnsMp[name[0]]
        setStrValue(v, val.Elem().Field(i))

    }
    return
}

//SelectS 查询
func (o *Orm) SelectS(tableName, condition string, data interface{}, arg ...interface{}) (err error) {
    val := reflect.ValueOf(data)

    if val.Kind() != reflect.Ptr && val.Kind() != reflect.Slice {
        return errors.New("no support type")
    }
    sInd := reflect.Indirect(val)
    typ := sInd.Type().Elem()
    list := reflect.MakeSlice(sInd.Type(), 0, 0)
    sql := getSelectsSQL(tableName, data)
    if sql == "" {
        return errors.New("sql error")
    }
    sql += condition

    rows, err := o.Query(sql, arg...)
    if err != nil {
        Error.Println(sql, arg, err)
        return err
    }
    Info.Println(sql, arg)
    defer rows.Close()

    columns, err := rows.Columns()
    if err != nil {
        return err
    }

    scans := make([]interface{}, len(columns))

    refs := make([][]byte, len(columns))
    for i := range columns {
        scans[i] = &refs[i]
    }
    for rows.Next() {

        err = rows.Scan(scans...)
        if err != nil {
            return err
        }

        d := reflect.New(sInd.Type().Elem())
        for i := 0; i < typ.NumField(); i++ {
            setStrValue(string(refs[i]), d.Elem().Field(i))
        }
        list = reflect.Append(list, d.Elem())
    }
    val.Elem().Set(list)
    return
}

//Update 修改数据库纪录
func (o *Orm) Update(tableName string, condition string, data interface{}, args ...interface{}) error {
    sql, v := getUpdateSQL(tableName, data)
    v = append(v, args...)
    sql += " " + condition + ";"
    _, err := o.Exec(sql, v...)
    if err != nil {
        Error.Println(sql, v, err)
        return err
    }
    Info.Println(sql, v)
    return nil

}

//UpdateStruct UpdateStruct
func (o *Orm) UpdateStruct(tableName string, data interface{}) error {
    sql, v := getUpdateStructSQL(tableName, data)
    _, err := o.Exec(sql, v...)
    if err != nil {
        Error.Println(sql, v, err)
        return err
    }
    Info.Println(sql, v)
    return nil
}

//Insert 添加数据库纪录
func (o *Orm) Insert(tableName string, data interface{}) error {
    sql, v := getInsertSQL(tableName, data)
    _, err := o.Exec(sql, v...)
    if err != nil {
        Error.Println(sql, v, err)
        return err
    }
    Info.Println(sql, v)
    return nil
}

//Delete Delete
func (o *Orm) Delete(tableName string, condition string, args ...interface{}) error {
    sql := "DELETE FROM " + tableName + " " + condition + ";"
    _, err := o.Exec(sql, args...)
    if err != nil {
        Error.Println(sql, args, err)
        return err
    }
    Info.Println(sql, args)
    return nil

}

//EXEC EXEC
func (o *Orm) EXEC(sql string, args ...interface{}) error {
    _, err := o.Exec(sql, args...)
    if err != nil {
        Error.Println(sql, args, err)
        return err
    }
    Info.Println(sql, args)
    return nil

}

func setStrValue(src string, val reflect.Value) error {
    if !val.CanSet() {
        return errors.New("can't set data")
    }
    switch val.Kind() {
    case reflect.Int, reflect.Int64:
        t, err := strconv.ParseInt(src, 10, 64)
        if err != nil {
            return err
        }
        val.SetInt(t)
    case reflect.String:
        val.SetString(src)
    case reflect.Float32, reflect.Float64:
        t, err := strconv.ParseFloat(src, 64)
        if err != nil {
            return err
        }
        val.SetFloat(t)

    case reflect.Bool:
        t, err := strconv.ParseBool(src)
        if err != nil {
            return err
        }
        val.SetBool(t)
    default:
        return errors.New("can't support type" + fmt.Sprint(val.Kind()))
    }
    return nil
}

// 判断值是否为空
func isEmptyValue(v reflect.Value) bool {
    if !v.IsValid() {
        return true
    }
    switch v.Kind() {
    case reflect.Array, reflect.Map, reflect.Slice, reflect.String:
        return v.Len() == 0
    case reflect.Bool:
        return !v.Bool()
    case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
        return v.Int() == 0
    case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64, reflect.Uintptr:
        return v.Uint() == 0
    case reflect.Float32, reflect.Float64:
        return v.Float() == 0
    case reflect.Interface, reflect.Ptr:
        return v.IsNil()
    }
    return false
}

//GetInsertSQL 获取插入的sql
func getInsertSQL(tableName string, data interface{}) (sql string, v []interface{}) {

    typ := reflect.TypeOf(data)
    val := reflect.ValueOf(data)
    if typ.Kind() != reflect.Ptr {
        return "", nil
    }

    sql = "INSERT INTO " + tableName + " ("
    var names []string
    var vales []string
    for i := 0; i < typ.Elem().NumField(); i++ {
        name := getFieldName(typ.Elem().Field(i), sqlTag)
        if name[0] == "-" {
            continue
        }
        if isEmptyValue(val.Elem().Field(i)) {
            if len(name) > 1 && (name[1] == "key" || name[1] == "null") {
                continue
            }
        }
        names = append(names, name[0])
        vales = append(vales, "?")
        v = append(v, val.Elem().Field(i).Interface())
    }
    sql += strings.Join(names, ",") + ") VALUES(" + strings.Join(vales, ",") + ");"
    return
}

//GetUpdateSQL 获取修改sql
func getUpdateSQL(tableName string, data interface{}) (sql string, v []interface{}) {
    typ := reflect.TypeOf(data)
    val := reflect.ValueOf(data)
    if typ.Kind() != reflect.Ptr {
        return "", nil
    }

    sql = "UPDATE " + tableName + " SET "
    var names []string
    for i := 0; i < typ.Elem().NumField(); i++ {
        name := getFieldName(typ.Elem().Field(i), sqlTag)
        if name[0] == "-" {
            continue
        }
        if isEmptyValue(val.Elem().Field(i)) {
            if len(name) > 1 && (name[1] == "key" || name[1] == "null") {
                continue
            }
        }
        names = append(names, name[0])
        v = append(v, val.Elem().Field(i).Interface())
    }
    sql += strings.Join(names, "=?,")
    if len(names) > 0 {
        sql += "=? "
    }
    return
}

//GetUpdateStructSQL 获取修改sql
func getUpdateStructSQL(tableName string, data interface{}) (sql string, v []interface{}) {
    typ := reflect.TypeOf(data)
    val := reflect.ValueOf(data)
    if typ.Kind() != reflect.Ptr {
        return "", nil
    }

    sql = "UPDATE " + tableName + " SET "

    var (
        valName   []string
        whereName []string
        whereVal  []interface{}
    )
    for i := 0; i < typ.Elem().NumField(); i++ {
        name := getFieldName(typ.Elem().Field(i), sqlTag)
        if name[0] == "-" {
            continue
        }
        if isEmptyValue(val.Elem().Field(i)) {
            if len(name) > 1 && (name[1] == "key" || name[1] == "null") {
                continue
            }
        }
        if len(name) > 1 && name[1] == "key" {
            whereVal = append(whereVal, val.Elem().Field(i).Interface())
            whereName = append(whereName, name[0])
            continue
        }
        valName = append(valName, name[0])
        v = append(v, val.Elem().Field(i).Interface())
    }

    sql += strings.Join(valName, "=?,")
    if len(valName) > 0 {
        sql += "=? "
    }
    if len(whereName) > 0 {
        v = append(v, whereVal...)
        sql += " WHERE "
        sql += strings.Join(whereName, "=? AND ")
        sql += "=?;"
    }

    return
}

//GetDeleteStructSQL GetDeleteStructSQL
func getDeleteStructSQL(tableName string, data interface{}) (sql string, v []interface{}) {
    typ := reflect.TypeOf(data)
    val := reflect.ValueOf(data)
    if typ.Kind() != reflect.Ptr {
        return "", nil
    }

    sql = "DELETE FROM " + tableName

    var (
        whereName []string
        whereVal  []interface{}
    )
    for i := 0; i < typ.Elem().NumField(); i++ {
        name := getFieldName(typ.Elem().Field(i), sqlTag)
        if len(name) > 1 && name[1] == "key" {
            whereVal = append(whereVal, val.Elem().Field(i).Interface())
            whereName = append(whereName, name[0])
            continue
        }
    }

    if len(whereName) > 0 {
        v = append(v, whereVal...)
        sql += " WHERE "
        sql += strings.Join(whereName, "=? AND ")
        sql += "=?;"
    }
    return sql, whereVal
}

//getSelectSQL 获取修改sql
func getSelectSQL(tableName string, data interface{}) (sql string) {
    typ := reflect.TypeOf(data)
    if typ.Kind() != reflect.Ptr {
        return ""
    }
    sql = "SELECT "
    var names []string
    for i := 0; i < typ.Elem().NumField(); i++ {
        name := getFieldName(typ.Elem().Field(i), sqlTag)
        if name[0] == "-" {
            continue
        }
        names = append(names, name[0])
    }
    sql += strings.Join(names, ",")
    if len(names) > 0 {
        sql += " FROM " + tableName + " "
    }
    return
}

//getSelectsSQL 获取修改sql
func getSelectsSQL(tableName string, data interface{}) (sql string) {
    val := reflect.ValueOf(data)
    if val.Kind() != reflect.Ptr && val.Kind() != reflect.Slice {
        return ""
    }
    sInd := reflect.Indirect(val)
    typ := sInd.Type().Elem()

    sql = "SELECT "
    var names []string
    for i := 0; i < typ.NumField(); i++ {
        name := getFieldName(typ.Field(i), sqlTag)
        if name[0] == "-" {
            continue
        }
        names = append(names, name[0])
    }
    sql += strings.Join(names, ",")
    if len(names) > 0 {
        sql += " FROM " + tableName + " "
    }
    return
}

//GetSelectsLIMITSQL 获取修改sql
func getSelectsLIMITSQL(tableName string, data interface{}) (sql string) {
    val := reflect.ValueOf(data)
    if val.Kind() != reflect.Ptr && val.Kind() != reflect.Slice {
        return ""
    }
    sInd := reflect.Indirect(val)
    typ := sInd.Type().Elem()

    sql = ""
    var names []string
    for i := 0; i < typ.NumField(); i++ {
        name := getFieldName(typ.Field(i), sqlTag)
        if name[0] == "-" {
            continue
        }
        names = append(names, name[0])
    }
    sql += strings.Join(names, ",")
    if len(names) > 0 {
        sql += " FROM " + tableName + " "
    }
    return
}

//getFieldName getFieldName
func getFieldName(filed reflect.StructField, tagName string) []string {
    name := filed.Tag.Get(tagName)
    if name == "" {
        name = strings.ToLower(filed.Name)
        return []string{name}
    }
    return strings.Split(name, ",")

}

//SpliceCondition 拼接Condition
func SpliceCondition(condition, str string) string {
    if strings.Index(condition, "WHERE") != -1 {
        return condition + " AND " + str
    }
    return condition + " WHERE " + str
}
